<?php
/***************************************************************
*  Copyright notice
*  (c) 2009, jianyuzhu@gmail.com
*  All rights reserved
*  This script is part of the PPEMI project.
***************************************************************/

//
require_once(CONFIG_PATH . CONFIG_DIR_LIB . 'adodb/adodb.inc.php');
require_once(CONFIG_PATH . CONFIG_DIR_LIB . 'adodb/adodb-xmlschema.inc.php');

class ADODBDatabase {
	var $database = null;
	var $dbType = null;
	var $dbHostName = null;
	var $userName=null;
	var $userPassword=null;
	var $dbName = null;
	var $lastmysqlrow = -1;
	
	//
	var $dieOnError = false;
	
	//query time
	var $queryTime = 0;
	
	//query count from the page start to the end
	//internal counter
	var $queryCount = 0;
	
	//constructor
	function &getInstance() {
		static $instance;
		if( !is_object($instance)) {
			$instance =& new ADODBDatabase();
		}
		return $instance;
	}
	
	function isMySQL() {
		return $this->dbType=='mysql';
	}
	
	function isOracle() {
		return $this->dbType=='oci8';
	}
	
	function println($msg) {
		//echo $msg;echo '<br>';
	}
	
	function setDieOnError($value) {
		$this->dieOnError = $value;
	}
	
	function setDatabaseType($type) {
		$this->dbType = $type;
	}
	
	function setDatabaseHost($host) {
		$this->dbHostName = $host;
	}
	
	function setUserName($name) {
		$this->userName = $name;
	}
	
	function setUserPassword($pass) {
		$this->userPassword = $pass;
	}
	
	function setDatabaseName($db) {
		$this->dbName = $db;
	}
	
	function getDataSourceName() {
		return 	$this->dbType. "://".$this->userName.":".$this->userPassword."@". $this->dbHostName . "/". $this->dbName;
	}
	
	function startTransaction() {
		$this->println("TRANS Started");
		$this->checkConnection();
		$this->database->StartTrans();
	}
	
	function completeTransaction() {
		if( $this->database->HasFailedTrans()) {
			$this->println("TRANS Rolled Back");
		} else {
			$this->println("TRANS Commited");
		}
		
		$this->database->CompleteTrans();
		$this->println("TRANS  Completed");
	}

	
	function checkError($msg='', $dieOnError=false) {
		if( $this->dieOnError || $dieOnError) {
         	$this->println("ADODB error ".$msg."->[".$this->database->ErrorNo()."]".$this->database->ErrorMsg());
			die ($msg."ADODB error ".$msg."->".$this->database->ErrorMsg());
		} else {
			$this->println("ADODB error ".$msg."->[".$this->database->ErrorNo()."]".$this->database->ErrorMsg());
		}
		return false;
	}
	
	function change_key_case($arr) {
		return is_array($arr)?array_change_key_case($arr):$arr;
	}
	
	/**
	* @return void
	* @desc checks if a connection exists if it does not it closes the connection
	 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
	 * All Rights Reserved..
	 * Contributor(s): ______________________________________..
	*/
	function checkConnection() {
		if(!isset($this->database)) {
			$this->println("TRANS creating new connection");
			$this->connect(false);
		} else {
			
		}
	}
	
	//query
	function query($sql, $dieOnError=false, $msg='') {
		$this->checkConnection();
		$result = & $this->database->Execute($sql);
		$this->lastmysqlrow = -1;
		if( !$result ) {
			$this->checkError($msg.' Query Failed:' . $sql . '::', $dieOnError);
		}
		
		//counter
		$this->addQueryCount();
		
		return $result;
	}
	
	function getEmptyBlob() {
		return 'null';
	}
	
	function updateBlob($tablename, $colname, $id, $data) {
		$this->println("updateBlob t=".$tablename." c=".$colname." id=".$id);
		$this->checkConnection();
		$result = $this->database->UpdateBlob($tablename, $colname, $data, $id);
		$this->println("updateBlob t=".$tablename." c=".$colname." id=".$id." status=".$result);
		return $result;
	}
	
	function limitQuery($sql,$start,$count, $dieOnError=false, $msg='') {
		$this->checkConnection();
		$result =& $this->database->SelectLimit($sql,$count,$start);
		if(!$result) $this->checkError($msg.' Limit Query Failed:' . $sql . '::', $dieOnError);
		return $result;
	}
	
	function getOne($sql, $dieOnError=false, $msg='') {
		$this->println("ADODB getOne sql=".$sql);
		$this->checkConnection();
		$result =& $this->database->GetOne($sql);
		if(!$result) $this->checkError($msg.' Get one Query Failed:' . $sql . '::', $dieOnError);
		return $result;
	}
	
	function getFieldsArray(&$result) {
		$field_array = array();
		if(! isset($result) || empty($result)) {
			return 0;
		}
		$i = 0;
		$n = $result->FieldCount();
		while ($i < $n) {
			$meta = $result->FetchField($i);
			if( !$meta) 
			{
				return 0;
			}
			array_push($field_array,$meta->name);
			$i++;
		}
		return $field_array;		
	}
	
	function getRowCount(&$result) {
		if(isset($result) && !empty($result)) {
			$rows= $result->RecordCount();
		}
		return $rows;		
	}

	/* ADODB newly added. replacement for mysql_num_rows */
	function num_rows(&$result) {
		return $this->getRowCount($result);
	}

	/* ADODB newly added. replacement form mysql_num_fields */
	function num_fields(&$result) {
		return $result->FieldCount();
	}

	/* ADODB newly added. replacement for mysql_fetch_array() */
	function fetch_array(&$result) {
		if( $result->EOF) {
			$this->println("ADODB fetch_array return null");
			return NULL;
		}		
		return $this->change_key_case($result->FetchRow());
	}

	/* ADODB newly added. replacement for mysql_result() */
	function query_result(&$result, $row, $col=0) {
		//$this->println("ADODB query_result r=".$row." c=".$col);
		$result->Move($row);
		$rowdata = $this->change_key_case($result->FetchRow());
		//$this->println($rowdata);
		$coldata = $rowdata[$col];
		//$this->println("ADODB query_result ". $coldata);
		return $coldata;
	}
	
	function getAffectedRowCount(&$result) {
		$this->println("ADODB getAffectedRowCount");
		$rows =$this->database->Affected_Rows(); 
		$this->println("ADODB getAffectedRowCount rows=".rows);
		return $rows;
	}
			

	function requireSingleResult($sql, $dieOnError=false,$msg='', $encode=true) {
			$result = $this->query($sql, $dieOnError, $msg);
			/*$row = $this->change_key_case($result->GetRowAssoc(false));
			$this->println("rsr ");
			$this->println($row);*/
			if( $this->getRowCount($result ) == 1)				
				//return to_html($result, $encode); // srini - just think $result compatability
				return $result;
			$this->log->error('Rows Returned:'. $this->getRowCount($result) .' More than 1 row returned for '. $sql);
			return '';
	}
	
	function fetchByAssoc(&$result, $rowNum = -1, $encode=true) {
		$this->println("ADODB fetchByAssoc ".$rowNum." fetch mode=".$adb->database->$ADODB_FETCH_MODE);
		if( $result->EOF) {
			$this->println("ADODB fetchByAssoc return null");
			return NULL;
		}
		if(isset($result) && $rowNum < 0) {
			$row = $this->change_key_case($result->GetRowAssoc(false));		
			$result->MoveNext();		
			$this->println("ADODB fetchByAssoc r< 0 isarray r=".is_array($row)." r1=".is_array($row[1]));		
			$this->println($row);
			if( $encode&& is_array($row))
				return array_map('to_html', $row);
			$this->println("ADODB fetchByAssoc r< 0 not array r1=".$row[1]);		
			return $row;		
		}
		$this->println("ADODB fetchByAssoc after if ".$rowNum);
		if( $this->getRowCount($result) > $rowNum) {
			$result->Move($rowNum);			
		}
		
		$this->lastmysqlrow = $rowNum; //srini - think about this
		$row = $this->change_key_case($result->GetRowAssoc(false));
		$result->MoveNext();
		//print_r($row);
		$this->println($row);
				
		if( $encode&& is_array($row))return array_map('to_html', $row);
		return $row;
	}
	
	function getNextRow(&$result, $encode=true) {
		$this->println("ADODB getNextRow");
		if(isset($result)) {
			$row = $this->change_key_case($result->FetchRow());
			if( $row && $encode&& is_array($row))return array_map('to_html', $row);
			return $row;
		}
		return null;
	}
	
	function fetch_row(&$result, $encode=true) {
		return $this->getNextRow($result);
	}
	
	function field_name(&$result, $col) {
		return $result->FetchField($col);
	}
	
	function getQueryTime() {
		return $this->queryTime;
	}
	
	//construction
	function ADODBDatabase($dbtype = '', $host = '', $dbname = '', $username = '', $passwd = '') {
        $this->println("ADODBDatabase");
		$this->initSettings();
		$this->resetSettings($dbtype, $host, $dbname, $username, $passwd);
	}
	
	function initSettings() {
		$this->db_type = CONFIG_DB_TYPE;
		$this->db_host = CONFIG_DB_HOST;
		$this->db_user = CONFIG_DB_USER;
		$this->db_password = CONFIG_DB_PASSWORD;
		$this->db_name = CONFIG_DB_NAME;
		$this->db_prefix = CONFIG_DB_PREFIX;
		$this->db_pconnect = CONFIG_DB_PCONNECT;
		$this->db_logsql = CONFIG_DB_LOGSQL;
		$this->db_logfile = CONFIG_DB_LOGFILE;
	}
	
    function resetSettings($dbtype = '', $host = '', $dbname = '', $username = '', $passwd = '') {
		if( $host == '' ) {
            $this->disconnect();
            $this->setDatabaseType($this->db_type);
            $this->setDatabaseHost($this->db_host);
            $this->setUserName($this->db_user);
            $this->setUserPassword($this->db_password);
            $this->setDatabaseName($this->db_name);
	    	if( $this->db_logsql ) {
				$this->enableSQLlog = ($this->db_logsql == true);
			}
            $this->println("resetSettings log=" . $this->enableSQLlog);
        } else {
            $this->disconnect();
            $this->setDatabaseType($dbtype);
            $this->setDatabaseHost($host);
            $this->setUserName($username);
            $this->setUserPassword($passwd);
            $this->setDatabaseName($dbname);
        }
	}
    
	function connect($dieOnError = false) {
		if(!isset($this->dbType)) {
			$this->println("ADODB Connect : DBType not specified");
			return;
		}
		$this->database = ADONewConnection($this->dbType);
		//$this->database->debug = true;
		$this->database->PConnect($this->dbHostName, $this->userName, $this->userPassword, $this->dbName);
		$this->database->LogSQL($this->enableSQLlog);
		//$this->database->SetFetchMode(ADODB_FETCH_ASSOC); 
		$this->println("ADODB type=".$this->dbType." host=".$this->dbHostName." dbname=".$this->dbName." user=".$this->userName." password=".$this->userPassword);
	}
	
	function quote($string) {
		return ADOConnection::qstr($string,get_magic_quotes_gpc());
	}
	
	function disconnect() {
		//$this->println("ADODB disconnect");
		//string -- do something
	}
	
	function setDebug($value) {
		$this->database->debug = $value;
	}
	
	// ADODB newly added methods
	function createTables($schemaFile, $dbHostName=false, $userName=false, $userPassword=false, $dbName=false, $dbType=false) {
		$this->println("ADODB createTables ".$schemaFile);
		if( $dbHostName!=false) $this->dbHostName=$dbHostName;
		if( $userName!=false) $this->userName=$userPassword;
		if( $userPassword!=false) $this->userPassword=$userPassword;
		if( $dbName!=false) $this->dbName=$dbName;
		if( $dbType!=false) $this->dbType=$dbType;
		
		//$db = ADONewConnection($this->dbType);
		$this->checkConnection();
		$db = $this->database;
		//$db->debug = true;
		
		//$this->println("ADODB createTables connect status=".$db->Connect($this->dbHostName, $this->userName, $this->userPassword, $this->dbName));
		$schema = new adoSchema( $db );
		$sql = $schema->ParseSchema( $schemaFile );
		
		$this->println("--------------Starting the table creation------------------");
		$this->println($sql);
		
		$result = $schema->ExecuteSchema( $sql, true );
		if( $result) {
			print $db->errorMsg();
		}
		$this->println("ADODB createTables ".$schemaFile." status=".$result);
		return $result;
	}
	
	function createTable($tablename, $flds) {
		$this->println("ADODB createTable table=".$tablename." flds=".$flds);
		$this->checkConnection();
		//$dict = NewDataDictionary(ADONewConnection($this->dbType));
		$dict = NewDataDictionary($this->database);
		$sqlarray = $dict->CreateTableSQL($tablename, $flds);
		$result = $dict->ExecuteSQLArray($sqlarray);
		$this->println("ADODB createTable table=".$tablename." flds=".$flds." status=".$result);
		return $result;
	}
	
	function alterTable($tablename, $flds, $oper) {
		$this->println("ADODB alterTableTable table=".$tablename." flds=".$flds." oper=".$oper);
		//$dict = NewDataDictionary(ADONewConnection($this->dbType));
		$this->checkConnection();
		$dict = NewDataDictionary($this->database);
		//$sqlarray = new Array(); 
		
		if( $oper == 'Add_Column') {
			$sqlarray = $dict->AddColumnSQL($tablename, $flds);
		} else if( $oper == 'Delete_Column') {
			$sqlarray = $dict->DropColumnSQL($tablename, $flds);
		}
		$this->println("sqlarray");
		$this->println($sqlarray);
		
		$result = $dict->ExecuteSQLArray($sqlarray);
		
		$this->println("ADODB alterTableTable table=".$tablename." flds=".$flds." oper=".$oper." status=".$result);
		return $result;
	}
	
	function getColumnNames($tablename) {
		$this->println("ADODB getColumnNames table=".$tablename);
		$this->checkConnection();
		$adoflds = $this->database->MetaColumns($tablename);
		//$colNames = new Array();
		$i=0;
		foreach($adoflds as $fld) {
			$colNames[$i] = $fld->name;
			$i++;
		}
		return $colNames;
	}
	
	function formatString($tablename,$fldname, $str) {
		//$this->println("ADODB formatString table=".$tablename." fldname=".$fldname." str=".$str);
		$this->checkConnection();
		$adoflds = $this->database->MetaColumns($tablename);
		
		foreach($adoflds as $fld) {
			//$this->println("ADODB formatString adofld =".$fld->name);
			if(strcasecmp($fld->name,$fldname)==0) {
				//$this->println("ADODB formatString fldname=".$fldname." fldtype =".$fld->type);
				$fldtype =strtoupper($fld->type); 	
				if(strcmp($fldtype,'CHAR')==0 || strcmp($fldtype,'VARCHAR') == 0 || strcmp($fldtype,'VARCHAR2') == 0 || strcmp($fldtype,'LONGTEXT')==0 || strcmp($fldtype,'TEXT')==0)
				{
					return $this->database->Quote($str);
					//$this->println("ADODB return else normal");
				} else if(strcmp($fldtype,'DATE') ==0 || strcmp($fldtype,'TIMESTAMP')==0) {
					return $this->formatDate($str);
				} else {	
					return $str;
				}
			}
		}
		$this->println("format String Illegal field name ".$fldname);
		return $str;
	}
	
	function formatDate($datetime) {
		$this->checkConnection();
		//$db = ADONewConnection($this->dbType);
		$db = &$this->database;
		$date = $db->DBTimeStamp($datetime);
		//if( $db->dbType=='mysql') return $this->quote($date);
		return $date;
	}
	
	function getDBDateString($datecolname) {
		$this->checkConnection();
		$db = &$this->database;
		$datestr = $db->SQLDate("Y-m-d, H:i:s" ,$datecolname);
		return $datestr;
	}
	
	function getUniqueID($seqname) {
		$this->checkConnection();
		return $this->database->GenID($seqname."_seq",1);
	}
	
	function get_tables() {
		$this->checkConnection();
		$result = & $this->database->MetaTables('TABLES');
		$this->println($result);
		return $result;
	}
	
	//query Count Operation
	function addQueryCount() {
		global $system_db_query_count;
		if( !isset($system_db_query_count) ) {
			$system_db_query_count = 0;
		}
		
		$system_db_query_count++;
		$this->queryCount++;
	}
	
	function getQueryCount() {
		global $system_db_query_count;
		
		if( isset($system_db_query_count) ) {
			return $system_db_query_count;
		}
		return $this->queryCount;
	}
}
//
?>